/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.NegativeKeywordKey;
import com.google.api.adwords.starterkit.db.structures.NegativeKeywordStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.v201101.cm.KeywordMatchType;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * The Class NegativeKeywordTable encapsulates most of the access to  
 * NegativeKeyword table in DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class NegativeKeywordTable {
  private PreparedStatement prepNew;
  private PreparedStatement prepUpdate;
  private PreparedStatement prepLoad;
  private PreparedStatement prepTouched;
  private PreparedStatement prepExists;
  private Connection conn;
  private PreparedStatement prepResetIsBanned;
  private PreparedStatement prepResetIsUpdated;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  /**
   * Instantiates a new negative keyword utils.
   *
   * @param conn the conn
   */
  public NegativeKeywordTable(Connection conn) {
    this.conn = conn;
    try {
      Statement stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS NegativeKeywords "
        + "  ( "
        + "     LocalId           INTEGER PRIMARY KEY, "
        + "     KeywordId         LONG DEFAULT 0, "
        + "     LocalCampaignId   LONG DEFAULT 0, "
        + "     CampaignId        LONG DEFAULT 0, "
        + "     MatchType         STRING DEFAULT '', "
        + "     Text              STRING DEFAULT '',"
        + "     IsTouched         BOOLEAN DEFAULT 0, "
        + "     IsUpdated         BOOLEAN DEFAULT 0, "
        + "     IsBanned          BOOLEAN DEFAULT 0, "
        + "     IsMatchModifiable BOOLEAN DEFAULT 1"
        + "  )");      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS NegativeKeywordsLocalCampaignidTextIndex " 
        + "ON NegativeKeywords (LocalCampaignId, Text);");
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS NegativeKeywordsKeywordIdCampaignIdIndex "
        + "ON NegativeKeywords (KeywordId, CampaignId);");

      prepExists = conn.prepareStatement(
          "SELECT Text FROM NegativeKeywords WHERE Text = ? " 
        + "AND LocalCampaignId = ? AND MatchType = ?;");
      prepLoad = conn.prepareStatement(
          "SELECT * FROM NegativeKeywords WHERE Text = ? " 
        + "AND LocalCampaignId = ? AND MatchType = ?;");  

      prepTouched = conn.prepareStatement(
          "UPDATE NegativeKeywords SET IsTouched = ? WHERE Text = ? " 
        + "AND LocalCampaignId = ? AND MatchType = ?;");  
      prepNew = conn.prepareStatement(
          "REPLACE INTO NegativeKeywords (KeywordId, CampaignId, " +
          "LocalCampaignId, MatchType, Text, IsMatchModifiable, IsBanned, " +
          "IsUpdated, IsTouched) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");  
      prepUpdate = conn.prepareStatement(
          "REPLACE INTO NegativeKeywords (LocalId, KeywordId, CampaignId, " +
          "LocalCampaignId, MatchType, Text, IsMatchModifiable, IsBanned, " +
          "IsUpdated, IsTouched) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); 
      prepResetIsBanned = conn.prepareStatement(
          "UPDATE NegativeKeywords SET IsBanned = ?");
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE NegativeKeywords SET IsUpdated = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Verify if a negative keywords exists on DB
   *
   * @param negativeKeywordKey the negative keyword key
   * @return true, if successful
   */
  public boolean exists(NegativeKeywordKey negativeKeywordKey) {
    boolean hashKey = false;
    ResultSet rs = null;
    try { 
      prepExists.setString(1, negativeKeywordKey.getText());      
      prepExists.setInt(2, negativeKeywordKey.getLocalCampaignId());
      prepExists.setString(3, 
          negativeKeywordKey.getMatchType().getValue());
      rs = prepExists.executeQuery();
      if (rs.next()) {
        hashKey = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return hashKey;
  }

  /**
   * Gets the negative keyword structure from a ResultSet
   *
   * @param rs the ResultSet
   * @return the keyword structure
   */
  public NegativeKeywordStructure getStructure(ResultSet rs) {
    NegativeKeywordStructure negKwdStructure = new NegativeKeywordStructure();
    try {
      negKwdStructure.setLocalId(rs.getInt("LocalId"));      
      negKwdStructure.setKeywordId(rs.getLong("KeywordId"));
      negKwdStructure.setCampaignId(rs.getInt("CampaignId"));
      negKwdStructure.setLocalCampaignId(rs.getInt("LocalCampaignId"));
      negKwdStructure.setMatchType(KeywordMatchType
          .fromString(rs.getString("MatchType").toUpperCase()));
      negKwdStructure.setText(rs.getString("Text"));
      negKwdStructure.setMatchModifiable(rs.getBoolean("IsMatchModifiable"));
      negKwdStructure.setBanned(rs.getBoolean("IsBanned"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return negKwdStructure;
  }

  /**
   * Load a negative keyword from DB
   *
   * @param negKwdKey the neg kwd primary key
   * @return the negative keyword structure
   */
  public NegativeKeywordStructure get(NegativeKeywordKey negKwdKey) {
    NegativeKeywordStructure negativeKeywordStructure = null;
    ResultSet rs = null;
    try {
      prepLoad.setString(1, negKwdKey.getText());
      prepLoad.setInt(2, negKwdKey.getLocalCampaignId());
      prepLoad.setString(3, negKwdKey.getMatchType().getValue());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        negativeKeywordStructure = getStructure(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return negativeKeywordStructure;
  }

  /**
   * Save a negative keyword into the DB
   *
   * @param negKwdStruct the neg kwd struct
   * @param updated the updated flag
   * @param touched the touched flag
   * @return the negative keyword structure with local id
   */
  public NegativeKeywordStructure save(NegativeKeywordStructure negKwdStruct, 
      boolean updated, boolean touched) {
    ResultSet rs = null;
    try {
      PreparedStatement prep;
      int count = 1;
      if (negKwdStruct.getLocalId() == 0) {
        prep = prepNew;
      } else {
        prep = prepUpdate;
        prep.setInt(count++, negKwdStruct.getLocalId());
      }
      prep.setLong(count++, negKwdStruct.getKeywordId());
      prep.setLong(count++, negKwdStruct.getCampaignId());
      prep.setInt(count++, negKwdStruct.getLocalCampaignId());
      prep.setString(count++, negKwdStruct.getMatchType().getValue());
      prep.setString(count++, negKwdStruct.getText());
      prep.setBoolean(count++, negKwdStruct.isMatchModifiable());
      prep.setBoolean(count++, negKwdStruct.isBanned());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, touched);
      prep.executeUpdate();

      rs = prep.getGeneratedKeys();      
      negKwdStruct.setLocalId(rs.getInt(1));      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return negKwdStruct;
  }

  /**
   * Save a negative keyword to a batch update in DB
   *
   * @param negKwdStruct the neg kwd struct
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void addToBatch(
      NegativeKeywordStructure negKwdStruct, boolean updated, boolean touched) {
    try {
      PreparedStatement prep;
      int count = 1;
      if (negKwdStruct.getLocalId() == 0) {
        prep = prepNew;
      } else {
        prep = prepUpdate;
        prep.setInt(count++, negKwdStruct.getLocalId());
      }
      prep.setLong(count++, negKwdStruct.getKeywordId());
      prep.setLong(count++, negKwdStruct.getCampaignId());
      prep.setInt(count++, negKwdStruct.getLocalCampaignId());
      prep.setString(count++, negKwdStruct.getMatchType().getValue());
      prep.setString(count++, negKwdStruct.getText());
      prep.setBoolean(count++, negKwdStruct.isMatchModifiable());
      prep.setBoolean(count++, negKwdStruct.isBanned());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, touched);
      prep.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Executes all pending batch updates 
   */
  public void execBatch() {
    try {
      prepNew.executeBatch();
      prepUpdate.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }
  
  /**
   * Sets the touched flag.
   *
   * @param negativeKeywordKey the negative keyword primary key
   * @param touched the touched flag
   */
  public void setTouched(NegativeKeywordKey negativeKeywordKey, 
      boolean touched) {
    try {
      prepTouched.setBoolean(1, touched);
      prepTouched.setString(2, negativeKeywordKey.getText());
      prepTouched.setInt(3, negativeKeywordKey.getLocalCampaignId());
      prepTouched.setString(4, negativeKeywordKey.getMatchType().getValue());
      prepTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Reset all flags on NegativeKeyword table to its default value
   */
  public void reset() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE NegativeKeywords SET IsUpdated = 0, IsTouched = 0, " 
        + "IsMatchModifiable = 1;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }        
  }
  
  /**
   * Update campaigns ids at ad group table
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId in NegativeKeywords table");    
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE NegativeKeywords SET CampaignId = ? "
          + "WHERE LocalCampaignId = ?");
      HashMap<Long, Integer> campaignsIdMap = getNewCampaignsIds();
      int count = 0;
      for (long campaignId : campaignsIdMap.keySet()) {
        int localCampaignId = campaignsIdMap.get(campaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
  }  
  
  /**
   * Gets the campaign ids map for new campaigns
   *
   * @return the campaign ids map
   */
  public HashMap<Long, Integer> getNewCampaignsIds() {
    log.debug("Loading Campaigns Ids needed by new NegativeKeywords");
    HashMap<Long, Integer> campaignsIdsMap = new HashMap<Long, Integer>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.LocalId, ca.CampaignId FROM Campaigns ca, "
          + "NegativeKeywords nk WHERE nk.LocalCampaignId = Ca.LocalId AND "
          + "nk.CampaignId = 0;");
      while (rs.next()) {
        campaignsIdsMap.put(rs.getLong("CampaignId"), rs.getInt("LocalId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
    return campaignsIdsMap;
  }
  
  /**
   * Return all NegativeKeyword modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains NegativeKeywords modified 
   */
  public ResultSet getModified() {
    log.debug("Loading DB");
    try {
      PreparedStatement prep = conn.prepareStatement(
          "SELECT * FROM NegativeKeywords WHERE IsUpdated = 1 " +
          "AND IsBanned = 0");
      return prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return null;
  }

  /**
   * Resets IsBanned flag in all rows of AdGroups table
   */
  public void resetIsBanned(boolean isBanned) {
    try {
      prepResetIsBanned.setBoolean(1, isBanned);
      prepResetIsBanned.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }
  
  public ResultSet getUnTouched() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
            "SELECT * FROM NegativeKeywords WHERE IsUpdated = 0 "
          + "AND IsTouched = 0 AND IsBanned = 0");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }
  
  public void updateKeywordId(long keywordId, 
      NegativeKeywordKey negativeKeywordKey) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE NegativeKeywords SET KeywordId = ? "
          + "WHERE Text = ? and LocalCampaignId = ? and MatchType = ?;");
      prep.setLong(1, keywordId);
      prep.setString(2, negativeKeywordKey.getText());
      prep.setLong(3, negativeKeywordKey.getLocalCampaignId());
      prep.setString(4, negativeKeywordKey.getMatchType().getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
  }
  
  public void updateKeywordId(long keywordId, long campaignId, 
      String text, KeywordMatchType matchType) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE NegativeKeywords SET KeywordId = ? "
          + "WHERE Text = ? and CampaignId = ? and MatchType = ?;");
      prep.setLong(1, keywordId);
      prep.setString(2, text);
      prep.setLong(3, campaignId);
      prep.setString(4, matchType.getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE NegativeKeywords SET IsUpdated = 0, IsTouched = 0");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
}
